#! /bin/bash

if [ -n "$1" ] ; then
      data_date="$1"
else
      data_date=$(date -d "-1 days" +%F)
fi

Event_LOG_CLEANED_SQL="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_cleaned ;
create table jtp_app_tmp.tmp_event_log_cleaned as
select * from jtp_app_ods.ods_event_app_log
where dt='$data_date' and deviceid is not null  and length(trim(deviceid)) != 0 and
    eventid is not null  and length(trim(eventid)) != 0 and
    sessionid is not null  and length(trim(sessionid)) != 0 and
    properties is not null  and size(properties) != 0
  and account is not null  and length(trim(account)) != 0 ;"

EVENT_LOG_SESSION_SQL="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_session ;
create  table jtp_app_tmp.tmp_event_log_session as
with t as (
    select account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude, longitude, nettype, osname, osversion, properties,
           releasechannel, resolution, sessionid, \`timestamp\`, dt,lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\` ) lagtime,
           (\`timestamp\`-lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\` )) difftime,
           (\`timestamp\`-lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\` ))/1000/60 fz,
           if((\`timestamp\`-lag(\`timestamp\`,1,0) over (partition by sessionid order by \`timestamp\` ))/1000/60 >= 10,\`timestamp\`,null) starttime
    from jtp_app_tmp.tmp_event_log_cleaned
), t1 as (
    select
        account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude, longitude, nettype, osname, osversion,
        properties, releasechannel,
        resolution, sessionid, \`timestamp\`,
        concat(deviceid,'_',last_value(starttime,true) over(partition by deviceid order by \`timestamp\`)) new_session_id,dt
    from t
)
select * from t1;"

EVENT_LOG_AREA_SQL="
drop table if exists jtp_app_tmp.tmp_event_log_dic;
create table jtp_app_tmp.tmp_event_log_dic as
with t0 as (
    select *,GeoHash(longitude,latitude) geo_hash from jtp_app_tmp.tmp_event_log_session
)
select t0.account, t0.appid, t0.appversion, t0.carrier, t0.deviceid, t0.devicetype, t0.eventid, t0.ip, t0.latitude, t0.longitude, t0.nettype,
       t0.osname, t0.osversion, t0.properties, t0.releasechannel,
       t0.resolution, t0.sessionid, t0.\`timestamp\`, t0.new_session_id,
       d.province,d.city,d.district,d.street
        ,t0.geo_hash, t0.dt from t0 inner join jtp_app_dim.dim_md_areas d
                                            on d.geo_hash = t0.geo_hash;"

EVENT_LOG_DETAIL_SQL="
insert overwrite table jtp_app_dwd.dwd_event_log_detail partition (dt='2024-11-18')
select
    province,
    city,
    district,
    street,
    deviceid,
    devicetype,
    new_session_id,
    \`timestamp\`
from
    jtp_app_tmp.tmp_event_log_dic
where dt='2024-11-18';"


/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${Event_LOG_CLEANED_SQL}${EVENT_LOG_SESSION_SQL}${EVENT_LOG_AREA_SQL}${EVENT_LOG_DETAIL_SQL}"